package server;

import java.awt.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.sound.midi.SysexMessage;
 
public class MySQLConnection {
 
    private static Connection conn = null;
    private Statement query = null;
    private String sql;
    private final static String DBHOST = "localhost";
    private final static String DBPORT = "3306";
	private final static String DATABASE = "worldofgothic";
    private final static String DBUSER = "root";
    private final static String DBPASSWORD = "IsTheBest0809";
 
    MySQLConnection() {
        try {
        	Class.forName("com.mysql.jdbc.Driver");
        	conn = DriverManager.getConnection("jdbc:mysql://" + DBHOST + ":" + DBPORT + "/" + DATABASE + "?" + "user=" + DBUSER + "&" + "password=" + DBPASSWORD);
        	query = conn.createStatement();
        } catch (ClassNotFoundException e) {
            System.out.println("Treiber nicht gefunden");
        } catch (SQLException e) {
            System.out.println("Connect nicht moeglich");
        }
    }
    public boolean add(String table,String feld,String wert) {
    	boolean result = false;
    	if(conn != null) {
    		try {
    			sql = "INSERT INTO `" + DATABASE + "`.`"+ table +"` (`"+ feld +"`) VALUES ('"+ wert +"');";
    			result = query.execute(sql);
    		} catch(SQLException e) {
    			e.printStackTrace();
    		}
    	}
    	return result;
    }
    public void updates(String sql) throws SQLException {
    	query.executeUpdate(sql);
    }
    public void add(String table,String[][] FelderWerte) {
    	//ResultSet result;
    	String feld = "";
    	String wert = "";
    	for(int i=0;i<FelderWerte.length;i++) {
    		if(FelderWerte[i][0] == null) break;
    		if(i!=0) {
    			feld = feld + ","; 
    			wert = wert + ",";
    		}
    		feld = feld + "`" + FelderWerte[i][0] + "`";
    		wert = wert + "'" + FelderWerte[i][1] + "'";
    	}
    	
    	if(conn != null) {
    		try {
    			sql = "INSERT INTO `" + DATABASE + "`.`" + table + "` (" + feld + ") VALUES (" + wert + ");";
    			query.executeUpdate(sql);
    			//System.out.println(result);
    			//System.out.println(result.getString("ID"));
    		} catch(SQLException e) {
    			e.printStackTrace();
    		}
    	}
    }
    public void delete(String table,String feld,String wert,int limit) {
    	if(conn != null) {
    		try {
    			sql = "DELETE FROM `" + table + "` WHERE `" + feld + "` = '" + wert + "' LIMIT " + limit;
    			query.execute(sql);
    		} catch(SQLException e) {
    			e.printStackTrace();
    		}
    	}
    }
    public void delete(String table,String feld,String wert) throws SQLException{
    	if(conn != null) {
    		sql = "DELETE FROM `" + table + "` WHERE `" + feld + "` = '" + wert + "' LIMIT 1";
    		query.execute(sql);
    	}
    }
    public ResultSet getResult(String sql) throws SQLException {
    	return  query.executeQuery(sql);
    }
    public void getName(String table,String felder)
    {
        if(conn != null)
        {
            try {
            	sql = "SELECT * FROM `"+ table +"`";
            	ResultSet result = query.executeQuery(sql);
            	while(result.next()) {
            		//System.out.println(result.last());
            		System.out.println(result.getString(felder));
            	}
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
